<?php
namespace App\Http\Controllers;


use App\Models\LampBook;
use App\Models\LampUser;
use Illuminate\Support\Facades\DB;
use Spatie\RouteAttributes\Attributes\Any;
use Spatie\RouteAttributes\Attributes\Prefix;

/**
 * 数据库操作
 * DB类 原生SQL查询
 * tableModel 表Model操作
 * Page()  表Model的分页操作
 */
#[Prefix('db')]
class DataController extends  Controller
{

    #[Any('updateOrInsert')]
    public function test()
    {
        // 更新或者插入
        DB::table('lamp_user')->updateOrInsert(
            ['username' => '张三'],  //updateOrInsert 存在就更新,不存在就插入一条记录
            ['gender' => '2', 'utime' => now()]
        );
    }
    function index()
    {
        //原生SQL，return对象返回json
        //$data['users'] = DB::select('select * from lamp_user');
        //return $data;
        //dump($data);

        //查询构造器
//        $data = DB::table('lamp_user')->get();

        //使用模型来创作数据库,return对象返回json
        //$data = LampUser::all();
        //dump($data);
        //return $data;

        /**查询构造器*/
//        $data = DB::table('lamp_user')->find(2);
//        $data = DB::table('lamp_user')->first();
//        $data = DB::table('lamp_user')->value('name'); //获取第一行的name字段
//        $data = DB::table('lamp_user')->pluck('name', 'username'); //返回列
        DB::table('lamp_user')->tosql();
        $data['sql'] = DB::table('lamp_user')->where(['gender'=>0])->tosql();

        //聚合
        $table = DB::table('lamp_user');
//        $data[] = $table->count();
//        $data[] = $table->avg('id');
//        $data[] = $table->where('id', 2)->get();
        $data[] = $table->where('id', 2)->exists();

        //select column
        //$data[] = $table->select('id','username')->get();
        //$data[] = DB::table('lamp_user')->select('id','username')->get();
  /*      $data[] = DB::table('lamp_user')
            ->groupBy('gender')
            ->havingRaw('gender>4')
            //->select(Db::raw('count(*) as count'), 'gender')
            ->selectRaw('count(*) as count, gender')
            ->get();*/

        //where的查询方法(fieldname value),(fieldname, operator, value)
        //where多个条件([fieldname, operator, value],[fieldname, operator, value])
        $table = DB::table('lamp_user');
        $data[] = $table->where('id', '<=', 4)->get();
        //$data[] = $table->where('username', 'like', "%qq%")->get();

        $data[] = $table->where(['gender'=> 0])->get();
        $data[] = $table->where([['gender', '=',0], ['id','<', 5]])->get();


        //where的派生查询
        $data[] = DB::table('lamp_user')->where('id', '<', '2')
            ->orWhere('gender', 0)
            ->toSql();

        $data[] = DB::table('lamp_user')->where('id', '<', '2')
            ->orWhere(function($query){
                $query->where('gender', '=',0)->where('username', 'like', "%qq%");
            })
            ->tosql();

        $data[] = DB::table('lamp_user')->whereBetween('id', ['10', '20'])->toSql();
        $data[] = DB::table('lamp_user')->whereNotBetween('id', ['10', '20'])->toSql();
        $data[] = DB::table('lamp_user')->whereIn('id', ['10', '20'])->toSql();
        $data[] = DB::table('lamp_user')->whereNull('name')->toSql();
        $data[] = DB::table('lamp_user')->whereColumn('name', 'username')->toSql();

        $data[] = DB::table('lamp_user')->inRandomOrder('id')->toSql();
        $data[] = DB::table('lamp_user')->limit('3')->offset(3)->toSql();

        //联表 内嵌查询
        $data[] = DB::table('lamp_user')->whereExists(function($query){
            //$query->from('lamp_book')->whereRaw('lamp_user.id=lamp_book.uid');
            $query->from('lamp_book')->selectRaw('1')->whereColumn('lamp_book.uid', 'lamp_user.id');
        })->toSql();

        $data[] = DB::table('lamp_user')->where('id', function($query){
            //$query->from('lamp_book')->whereRaw('lamp_user.id=lamp_book.uid');
            $query->from('lamp_book')->select('id')->whereColumn('lamp_book.uid', 'lamp_user.id');
        })->toSql();

        //联表 联表查询
        $data[] = DB::table('lamp_user')
            //->join('lamp_book', 'lamp_book.uid', '=', 'lamp_user.id')
            //->leftJoin('lamp_book', 'lamp_book.uid', '=', 'lamp_user.id')
            ->rightJoin('lamp_book', 'lamp_book.uid', '=', 'lamp_user.id')
            //->crossJoin('lamp_book', 'lamp_book.uid', '=', 'lamp_user.id')
             ->select('lamp_user.id', 'lamp_book.bookname')
            ->toSql();

        $data[] = DB::table('lamp_user')->join('lamp_book', function($join) {
            $join->on('lamp_book.uid', '=', 'lamp_user.id');
        })->toSql();


        //构造器增删改
        $data = [];
       /* $data = DB::table('lamp_user')->insert([
            'username' => 'libai',
            'name' => '李白',
            'password' => md5('123'),
        ]);*/

        $data['insertOrIgnore'] = DB::table('lamp_user')->insertOrIgnore([
            [
                'username' => 'xiaobai',
                'name' => '李白',
                'password' => md5('123'),
            ],
            [
                'username' => 'taibai',
                'name' => '李白',
                'password' => md5('123'),
            ]
        ]);


        $data['insert'] = DB::table('lamp_book')->insertOrIgnore([
            [
                'id' => 1,
                'uid' => '1',
                'bookname' => '爱别离',
            ],
            [
                'id' => 2,
                'uid' => '2',
                'bookname' => '广岛之恋',
            ],
            [
                'id' => 3,
                'uid' => '10',
                'bookname' => '小王子',
            ],
        ]);

      $data['insertGetId'] = DB::table('lamp_book')->insertGetId([
          'uid' => '2',
          'bookname' => '人才管理',
      ]);

        $data = DB::table('lamp_user')->where('id', 5)
            ->update(['name' => '力宏', 'gender' => 1]);
//
        $data = DB::table('lamp_user')->updateOrInsert(['id' => 8],['username' => 'lihei','name' => '李黑', 'gender' => 1]);
//
//        $data = DB::table('lamp_book')->where('id', 2)->increment('star', 2);

        //删除表记录
        //$data['delete'] = Db::table('lamp_book')->delete();
        //$data['delete id'] = Db::table('lamp_book')->delete(10);
        $data['delete id'] = Db::table('lamp_book')->where('id', 35)->delete();



        //分块 获取所有数据，每次取N条，处理
        /*DB::table('lamp_user')->orderBy('id', 'DESC')->chunk(3, function($list) {
            foreach ($list as $row) {
                echo $row->id . ',';
            }
            echo "<br/>";
        });*/

        dump($data);

        //将对象转化为reponse对象,return输出json
        $user = response()->json($data);
        //var_dump($user);
        //return $user;

    }

    #[Any('model-select')]
    function modelSelect()
    {
        $idCollect = collect([1,2]);
        $data[] = LampUser::query()->whereKey($idCollect)->get(['id','name','username']); //获取所有

        $data['pluck']  = LampUser::query()->limit(5)->pluck('id');
        return $data;
    }
    /**使用数据表模型 基本和查询构造器一样的方法*/
    function tableModel()
    {

        //$data = LampUser::all(); //获取所有
        $data[] = LampBook::where(
            ['uid' => 2])->limit(1)->get();



        //增删改
        $model = new LampBook();
        $model->uid =10;
        $model->bookname = '乡村blues';
        $data[] = $model->save(); //返回bool true 成功




        $model = LampUser::find(4);
        $model->name = '故前方';
        $model->save();

        LampUser::where('name', '李白')->update(['name' => '小弟']);

        LampBook::create(
            ['uid' => 11,
            'bookname' => '蓝调blues',]
        );

        $model = LampBook::find(48);
        if ($model) {
            $model->delete();
        }

        //LampBook::where('bookname', '乡村blues')->delete();

        $data['destry'] = LampBook::destroy(60);

        $data['destry arr'] = LampBook::destroy([52,53,54]);

        //数据表关联 1对多
        $data = LampUser::find(1)->book;
        //$data = LampUser::find(1)->book()->get();

        $data = [];
        //拥有一条或者多条的用户
        $data[] = LampUser::has('book')->get();
        $data[] = LampUser::has('book')->toSql();  //打印SQL

        $data[] = LampUser::has('book', '>', '1')->toSql();


        $data = LampUser::with('book')->get();
        dump($data);
        return view('def');
        //dd($data);
        return $data;
    }

    //分页
    function page()
    {
        //$list = Db::table('lamp_user')->paginate(3);
        //dd($list);
        $pageData = LampUser::paginate(3);
        //LampUser::forPage()
        return view('page', ['list' => $pageData]);

    }
}
